package com.kaifamiao.wendao.dao;

import com.kaifamiao.utils.JdbcHelper;
import com.kaifamiao.utils.RowHandler;
import com.kaifamiao.wendao.entity.Customer;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.time.LocalDateTime;
import java.util.List;

/**
 * CustomerDao类的实例是 Customer类 与 t_customers 表之间的桥梁:
 * 1、可以将 Customer类的实例 保存到 t_customers 表中
 * 2、可以读取 t_customers 表中 并将其装配成 Customer类的实例
 */
public class CustomerDao implements Dao< Customer , Integer >{

    private RowHandler<Customer> rowHandler = new RowHandler<>(){
        @Override
        public Customer handle(ResultSet rs) throws SQLException {
            Customer c = new Customer();
            c.setId( rs.getInt( "id" ) );
            c.setUsername( rs.getString( "username" ) );
            c.setPassword( rs.getString( "password" ) );
            c.setSalt( rs.getString( "salt" ) );
            c.setNickname( rs.getString( "nickname" ) );

            Timestamp ts = rs.getTimestamp( "sign_up_time" );
            LocalDateTime sut = ts.toLocalDateTime() ;
            c.setSignUpTime( sut );

            c.setSignUpIp( rs.getString( "sign_up_ip" ) );
            return c;
        }
    };

    /**
     * 根据id查询数据库中的记录并将其包装成一个Customer对象
     * @param id 需要查询的记录的主键值
     * @return 返回数据库表中记录对应的Customer对象，若在数据库中未找到对应数据则返回 null
     */
    public Customer find( Integer id ) {
        JdbcHelper helper = JdbcHelper.getInstance();
        String sql = "SELECT id , username , password , salt , nickname , sign_up_time , sign_up_ip " +
                     "FROM t_customers " +
                     "WHERE id = ? " ;
        Customer c = helper.single( sql , rowHandler , id );
        helper.release();
        return c ;
    }

    /**
     * 根据username查询数据库中的记录并将其包装成一个Customer对象
     * @param username 需要查询的记录的主键值
     * @return 返回数据库表中记录对应的Customer对象，若在数据库中未找到对应数据则返回 null
     */
    public Customer find( String username ) {
        JdbcHelper helper = JdbcHelper.getInstance();
        String sql = "SELECT id , username , password , salt , nickname , sign_up_time , sign_up_ip " +
                     "FROM t_customers " +
                     "WHERE username = ? " ;
        Customer c = helper.single( sql , rowHandler , username );
        helper.release();
        return c ;
    }

    /**
     * 查询数据库表中所有数据 (根据id排序)
     * @return 返回与数据库表中各行数据对应的Customer对象组成的List集合
     */
    public List<Customer> findAll() {
        JdbcHelper helper = JdbcHelper.getInstance();
        String sql = "SELECT id , username , password , salt , nickname , sign_up_time , sign_up_ip " +
                     "FROM t_customers " +
                     "ORDER BY id";
        List<Customer> list = helper.query( sql , rowHandler );
        helper.release();
        return list ;
    }

    /**
     * 查询数据库返回新的数据库主键值
     * @return
     */
    public Integer nextId(){
        JdbcHelper helper = JdbcHelper.getInstance();
        // MySQL
        String sql = "SELECT IFNULL( max( id ) , 0 ) + 1 FROM t_customers" ;
        // Oracle
        // String sql = "SELECT NVL( max( id ) , 0 ) + 1 FROM t_customers" ;
        Integer id = helper.single(sql, new RowHandler<Integer>() {
            @Override
            public Integer handle(ResultSet rs) throws SQLException {
                return rs.getInt( 1 );
            }
        });
        helper.release();
        return id ;
    }

    /**
     * 将 Customer 对象中所包含的数据保存到数据库中
     * @param c 被保存的 Customer 对象
     * @return 仅当保存成功时返回 true ，否则返回 false
     */
    public boolean save( Customer c ) {
        JdbcHelper helper = JdbcHelper.getInstance();
        String sql = "INSERT INTO t_customers " +
                     "( id , username , password , salt , nickname , sign_up_time , sign_up_ip )" +
                     " VALUES ( ? , ? , ? , ? , ? , ? , ? )" ;
        LocalDateTime dt = c.getSignUpTime() ;
        Timestamp signUpTime = dt == null ? null : Timestamp.valueOf( dt );
        Object[] params = { c.getId() , c.getUsername() , c.getPassword() , c.getSalt() , c.getNickname() , signUpTime , c.getSignUpIp() };
        int count = helper.execute( sql , params );
        helper.release();
        // 因为仅插入一条数据，因此 count 为 1 就表示成功
        return count == 1 ;
    }

    /**
     * 根据参数指定的对象来更新数据库表中的数据
     * @param c 被修改的对象(该对象中的数据需要同步到数据库中)
     * @return 仅当修改成功时返回 true ，否则返回 false
     */
    public boolean update( Customer c ) {
        JdbcHelper helper = JdbcHelper.getInstance();
        String sql = "UPDATE t_customers " +
                     "SET username = ? , password = ? , nickname = ? " +
                     "WHERE id = ? " ;
        int count = helper.execute( sql , c.getUsername() , c.getPassword() , c.getNickname() , c.getId() );
        helper.release();
        return count == 1 ;
    }

    /**
     * 根据客户id来删除客户信息
     * @param id 被删除客户的id
     * @return 仅当成功删除指定id对应的用户时返回 true ，否则返回 false
     */
    public boolean remove( Integer id ) {
        String sql = "DELETE FROM t_customers WHERE id = ? " ;
        JdbcHelper helper = JdbcHelper.getInstance();
        int count = helper.execute( sql , id );
        helper.release();
        return count == 1 ;
    }

    /**
     * 根据登录名称来删除客户信息
     * @param username 被删除客户的登录名
     * @return 仅当成功删除指定登录名对应的用户时返回 true ，否则返回 false
     */
    public boolean remove( String username ) {
        String sql = "DELETE FROM t_customers WHERE username = ? " ;
        JdbcHelper helper = JdbcHelper.getInstance();
        int count = helper.execute( sql , username );
        helper.release();
        return count == 1 ;
    }

}
